﻿alter table Cms_Wsm_InstrumentAssembly
add DayEstimate decimal(18,2)

go
--发票试图
create view dbo.vw_Invoice
as
select 
a.cCuscode
,a.cBVCode
,a.dDate
,a.idDueDte
,a.iNatSum
,a.iExchSum
,DATEADD(month,isnull(v.Age,0),a.idDueDte) as Duedate
,case when a.iNatSum>iExchSum and DATEDIFF(day,getdate(),DATEADD(month,isnull(v.Age,0),a.idDueDte)) <=30 and DATEDIFF(day,getdate(),DATEADD(month,isnull(v.Age,0),a.idDueDte)) >=0 then 1
	when a.iNatSum>iExchSum and DATEDIFF(day,getdate(),DATEADD(month,isnull(v.Age,0),a.idDueDte)) <0  then 2
	else 0 end as alertType
from Cms_Ctrt_Bill a
inner join Cms_Ctrt_Customer b on a.cCusCode = b.CustomerCode
left join vw_CustomerAge v on b.CustomerID = v.CustomerID

go

--装机详情中添加审核状态
alter table Cms_Wsm_MaintainBill add CheckerID uniqueidentifier
alter table Cms_Wsm_MaintainBill add CheckerName varchar(50)
alter table Cms_Wsm_MaintainBill add CheckAt datetime

GO

--装机几个频率
alter table Cms_Wsm_InstrumentAssembly alter column CalibrationFrequency decimal(5,2)
alter table Cms_Wsm_InstrumentAssembly alter column GuaranteeFrequency decimal(5,2)
alter table Cms_Wsm_InstrumentAssembly alter column ReturnVisitFrequency decimal(5,2)

go


alter view accrssoryInInstrument
 as
 select a.*,b.InstrumentID from dbo.Cms_Wsm_AccessoryInfo as a inner join Cms_Wsm_AccessoryInInstrument as b on b.AccessoryID = a.AccessoryID

go


alter table Cms_Wsm_AccessoryInfo
alter column accessoryName varchar(100)

go


alter table Cms_Wsm_MaintainBill 
alter column ServicedStatus varchar(100)

go


ALTER proc [dbo].[CreateBillForCycle]
as
begin

DECLARE @CustomerID uniqueidentifier
		,@TaskType int		 
		,@PerformPerson uniqueidentifier
		,@InstrumentID uniqueidentifier			 
		,@InstrumentType uniqueidentifier
		,@InstrumentSerialNum varchar(50)
		,@GuaranteeFrequency decimal(18,2)
		,@ReturnVisitFrequency decimal(18,2)	 
		,@CalibrationFrequency decimal(18,2)
		,@InstrumentBeginUse datetime
		
	DECLARE cur_bill CURSOR FOR 	
		SELECT 		 				 
			a.CustomerID
			,a.TaskType			 
			,b.Maintainer as PerformPerson			
			,a.InstrumentID		
			,a.InstrumentType
			,a.InstrumentSerialNum
			,case when isnull(c.GuaranteeFrequency,0) = 0 then 120 else c.GuaranteeFrequency end GuaranteeFrequency
			,case when isnull(c.ReturnVisitFrequency,0) = 0 then 120 else c.ReturnVisitFrequency end ReturnVisitFrequency
			,case when isnull(c.CalibrationFrequency,0) = 0 then 120 else c.CalibrationFrequency end CalibrationFrequency
			,c.InstrumentBeginUse
		FROM Cms_Wsm_TaskRegister as a 
		inner join Cms_Wsm_MaintainBill b 
		on a.RegisterID = b.RegisterID
		inner join Cms_Wsm_InstrumentAssembly c 
		on c.MaintainID = b.MaintainID
		where a.TaskType = 1 and a.TaskStatus = 1
		
	OPEN cur_bill
	FETCH next FROM cur_bill
	INTO @CustomerID,@TaskType,@PerformPerson,@InstrumentID,@InstrumentType,
			@InstrumentSerialNum,@GuaranteeFrequency,@ReturnVisitFrequency,
			@CalibrationFrequency,@InstrumentBeginUse
	
	WHILE @@FETCH_STATUS = 0
	BEGIN
		declare @GuaranteeDate varchar(50)
		declare @ReturnVisitDate varchar(50)
		declare @CalibrationDate varchar(50)
		declare @DateNow varchar(50)
		set @GuaranteeDate = convert(varchar(12),DateAdd(month,cast(@GuaranteeFrequency as int), @InstrumentBeginUse),112)
		set @ReturnVisitDate = convert(varchar(12),DateAdd(month,cast(@ReturnVisitFrequency as int), @InstrumentBeginUse),112)
		set @CalibrationDate = convert(varchar(12),DateAdd(month,cast(@CalibrationFrequency as int), @InstrumentBeginUse),112)
		set @DateNow = convert(varchar(12), GETDATE(),112)
		IF ((DATEDIFF(month,@InstrumentBeginUse,@DateNow) % @GuaranteeFrequency) = 0 and DATEDIFF(month,@InstrumentBeginUse,getdate()) > 0)
			BEGIN 
				 insert into Cms_Wsm_TaskRegister values(NEWID(),null,null,3,0,@CustomerID,dateadd(day,1,getdate()),
				 @PerformPerson,GETDATE(),null,@InstrumentID,@InstrumentType,@InstrumentSerialNum,0,GETDATE())
			END 
		IF ((DATEDIFF(month,@InstrumentBeginUse,@DateNow) % @ReturnVisitDate) = 0  and DATEDIFF(month,@InstrumentBeginUse,getdate()) > 0)
			BEGIN 
				 insert into Cms_Wsm_TaskRegister values(NEWID(),null,null,7,0,@CustomerID,dateadd(day,1,getdate()),
				 @PerformPerson,GETDATE(),null,@InstrumentID,@InstrumentType,@InstrumentSerialNum,0,GETDATE())
			END 
		IF ((DATEDIFF(month,@InstrumentBeginUse,@DateNow) % @CalibrationDate) = 0 and DATEDIFF(month,@InstrumentBeginUse,getdate()) > 0)
			BEGIN 
				 insert into Cms_Wsm_TaskRegister values(NEWID(),null,null,6,0,@CustomerID,dateadd(day,1,getdate()),
				 @PerformPerson,GETDATE(),null,@InstrumentID,@InstrumentType,@InstrumentSerialNum,0,GETDATE())
			END 
		FETCH next FROM cur_bill
			INTO @CustomerID,@TaskType,@PerformPerson,@InstrumentID,@InstrumentType,
				@InstrumentSerialNum,@GuaranteeFrequency,@ReturnVisitFrequency,
				@CalibrationFrequency,@InstrumentBeginUse
	END
	CLOSE cur_bill
	DEALLOCATE cur_bill
end


go



alter proc [dbo].[CreateBillForCycle]
as
begin

DECLARE @CustomerID uniqueidentifier
		,@TaskType int		 
		,@PerformPerson uniqueidentifier
		,@InstrumentID uniqueidentifier			 
		,@InstrumentType uniqueidentifier
		,@InstrumentSerialNum varchar(50)
		,@GuaranteeFrequency decimal(18,2)
		,@ReturnVisitFrequency decimal(18,2)	 
		,@CalibrationFrequency decimal(18,2)
		,@InstrumentBeginUse datetime
		
	DECLARE cur_bill CURSOR FOR 	
		SELECT 		 				 
			a.CustomerID
			,a.TaskType			 
			,b.Maintainer as PerformPerson			
			,a.InstrumentID		
			,a.InstrumentType
			,a.InstrumentSerialNum
			,case when isnull(c.GuaranteeFrequency,0) = 0 then 120 else c.GuaranteeFrequency end GuaranteeFrequency
			,case when isnull(c.ReturnVisitFrequency,0) = 0 then 120 else c.ReturnVisitFrequency end ReturnVisitFrequency
			,case when isnull(c.CalibrationFrequency,0) = 0 then 120 else c.CalibrationFrequency end CalibrationFrequency
			,c.InstrumentBeginUse
		FROM Cms_Wsm_TaskRegister as a 
		inner join Cms_Wsm_MaintainBill b 
		on a.RegisterID = b.RegisterID
		inner join Cms_Wsm_InstrumentAssembly c 
		on c.MaintainID = b.MaintainID
		where a.TaskType = 1 and a.TaskStatus = 1
		
	OPEN cur_bill
	FETCH next FROM cur_bill
	INTO @CustomerID,@TaskType,@PerformPerson,@InstrumentID,@InstrumentType,
			@InstrumentSerialNum,@GuaranteeFrequency,@ReturnVisitFrequency,
			@CalibrationFrequency,@InstrumentBeginUse
	
	WHILE @@FETCH_STATUS = 0
	BEGIN
		declare @GuaranteeDate varchar(50)
		declare @ReturnVisitDate varchar(50)
		declare @CalibrationDate varchar(50)
		declare @DateNow varchar(50)
		set @GuaranteeDate = convert(varchar(12),DateAdd(month,cast(@GuaranteeFrequency as int), @InstrumentBeginUse),112)
		set @ReturnVisitDate = convert(varchar(12),DateAdd(month,cast(@ReturnVisitFrequency as int), @InstrumentBeginUse),112)
		set @CalibrationDate = convert(varchar(12),DateAdd(month,cast(@CalibrationFrequency as int), @InstrumentBeginUse),112)
		set @DateNow = convert(varchar(12), GETDATE(),112)
		IF ((DATEDIFF(month,@InstrumentBeginUse,@DateNow) % @GuaranteeFrequency) = 0 and DATEDIFF(month,@InstrumentBeginUse,getdate()) > 0 and Substring(cast(@GuaranteeFrequency as varchar),CHARINDEX('.',cast(@GuaranteeFrequency as varchar))+1,Len(cast(@GuaranteeFrequency as varchar))-CHARINDEX('.',cast(@GuaranteeFrequency as varchar)))='00' )
			BEGIN 
				 insert into Cms_Wsm_TaskRegister values(NEWID(),null,null,3,0,@CustomerID,dateadd(month,DATEDIFF(month,@InstrumentBeginUse,@DateNow),@InstrumentBeginUse),
				 @PerformPerson,GETDATE(),null,@InstrumentID,@InstrumentType,@InstrumentSerialNum,0,GETDATE())
			END 
		IF ((DATEDIFF(month,@InstrumentBeginUse,@DateNow) % @ReturnVisitFrequency) = 0  and DATEDIFF(month,@InstrumentBeginUse,getdate()) > 0 and Substring(cast(@ReturnVisitDate as varchar),CHARINDEX('.',cast(@ReturnVisitDate as varchar))+1,Len(cast(@ReturnVisitDate as varchar))-CHARINDEX('.',cast(@ReturnVisitDate as varchar)))='00')
			BEGIN 
				 insert into Cms_Wsm_TaskRegister values(NEWID(),null,null,7,0,@CustomerID,dateadd(month,DATEDIFF(month,@InstrumentBeginUse,@DateNow),@InstrumentBeginUse),
				 @PerformPerson,GETDATE(),null,@InstrumentID,@InstrumentType,@InstrumentSerialNum,0,GETDATE())
			END 
		IF ((DATEDIFF(month,@InstrumentBeginUse,@DateNow) % @CalibrationFrequency) = 0 and DATEDIFF(month,@InstrumentBeginUse,getdate()) > 0 and Substring(cast(@CalibrationFrequency as varchar),CHARINDEX('.',cast(@CalibrationFrequency as varchar))+1,Len(cast(@CalibrationFrequency as varchar))-CHARINDEX('.',cast(@CalibrationFrequency as varchar)))='00')
			BEGIN 
				 insert into Cms_Wsm_TaskRegister values(NEWID(),null,null,6,0,@CustomerID,dateadd(month,DATEDIFF(month,@InstrumentBeginUse,@DateNow),@InstrumentBeginUse),
				 @PerformPerson,GETDATE(),null,@InstrumentID,@InstrumentType,@InstrumentSerialNum,0,GETDATE())
			END 
		FETCH next FROM cur_bill
			INTO @CustomerID,@TaskType,@PerformPerson,@InstrumentID,@InstrumentType,
				@InstrumentSerialNum,@GuaranteeFrequency,@ReturnVisitFrequency,
				@CalibrationFrequency,@InstrumentBeginUse
	END
	CLOSE cur_bill
	DEALLOCATE cur_bill
end


go

--2011-06-18----
alter table Cms_Wsm_AccessoryInInstrument
add Deleted bit null

go


--2011-06-22  入库单、出库单添加属性
alter table Cms_Wsm_AccessoryStockIn
Add supplyno varchar(20) null --配件供应单号

go
alter table Cms_Wsm_AccessoryStockIn
Add Memo nvarchar(100) null --备注

go

alter table Cms_Wsm_AccessoryStock
Add StockSource nvarchar(20) null --配件来源

go

alter table Cms_Wsm_StockInOut
Add StockSource nvarchar(20) null --配件来源


go

alter table Cms_Wsm_AccessoryOut
Add Memo nvarchar(100) null --备注


go



-------------------
---2011-07-04
-------------------

INSERT INTO [Cms_Sys_Function]([FuncID], [FFuncID], [FuncName], [IsMenu], [URL], [ImageUrl], [Target], [Description], [Deleted]) 
VALUES(N'Func5093', N'Func5090', N'更新装机信息', N'0', N'ContractFollowUp/CustomerInstrumentList.aspx', N'ico_18_10002.gif', N'mainframe', NULL, 0)

go

alter view [dbo].[CustomerInstrument] 
as
SELECT  
	a.instrumentID
	,a.instrumentName
	,a.instrumentCode
	,a.categoryGUID
	,convert(char(10),c.InstallDate,121) as InstallDate
	,convert(char(10),c.InstrumentBeginUse,121) as InstrumentBeginUse
	,c.SerialNum
	,cu.CustomerName
	,cu.CustomerAddress
	,cue.BelongToTeam
	,cue.BelongToSalor
	,cue.CustomerArea
	,cu.CustomerID
	,c.AssemblyID
	,b.RegisterID
	,e.MaintainID
	,c.InstrumentStatus
	,case c.InstrumentStatus 
		when 1 then '正常使用'
		when 2 then '暂停使用'
		when 3 then '撤回'
		when 4 then '调试培训中'
		when 5 then '停用'
	 End as InstrumentStatusName
from Cms_Wsm_InstrumentInfo as a with(nolock) 
inner join Cms_Wsm_TaskRegister b with(nolock) on a.InstrumentID = b.InstrumentID 
inner join Cms_Wsm_MaintainBill e with(nolock) on b.RegisterID = e.RegisterID
inner join Cms_Wsm_InstrumentAssembly c with(nolock) on c.MaintainID = e.MaintainID 
inner join Cms_Ctrt_Customer cu  with(nolock) on cu.CustomerID = b.CustomerID 
left join Cms_Wsm_CustomerExtension cue with(nolock) on cue.CustomerID = b.CustomerID
where b.TaskType = 1

go

create view dbo.vw_maintainbill_export
as
select
A.PaperInvoicesNo as [服务单号]
,c.CustomerName as [客户名称]
,c1.CustomerArea as [所在区域]
,u.UserName	 as [现场工程师]
,case B.TaskType
	when 1 then '安装'
	when 2 then '维修FE'
	when 3 then '保养'
	when 4 then '培训'
	when 5 then '升级'
	when 6 then '校准'
	when 7 then '回访'
	when 8 then '开放系统调试'
	when 9 then '维护LS'
	End as [工作类型]
,A.WorkContent as [工作内容]
,A.BeginDate as [到达时间]
,A.EndTime as [结束时间]
,i.instrumentName as [仪器名称]
,B.InstrumentSerialNum as [仪器序列号]
,A.Maintainer
,B.CustomerID
,B.InstrumentSerialNum
,B.InstrumentID
,B.TaskType
,A.BeginDate
,A.EndTime
From Cms_Wsm_MaintainBill A  with(nolock)
join Cms_Wsm_TaskRegister B with(nolock) on A.RegisterID = B.RegisterID
inner join Cms_Ctrt_Customer c with(nolock) on c.CustomerID = B.CustomerID
inner join Cms_Wsm_CustomerExtension c1  with(nolock) on c1.CustomerID = c.CustomerID
inner join Cms_Sys_User u with(nolock) on u.UserID = A.Maintainer
inner join Cms_Wsm_InstrumentInfo i with(nolock) on i.InstrumentID = B.InstrumentID